package br.com.fiap.si.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import br.com.fiap.factory.ConnectionFactory;
import br.com.fiap.si.bean.ContaPagar;
import br.com.fiap.si.bean.ContaReceber;

public class ContaPagarDAO {
	Connection conn = null;

	public ContaPagarDAO() { // construtor
		try {
			conn = ConnectionFactory.getConnection();
		} catch (SQLException ex) {
		}
	}

	public void insert(ContaPagar Deb) {

		String sql = "insert into contapagar(valor,descricao,datainclusao,datavencimento,parcelas,tipo,idusuario) "
				+ "values(?,?,STR_TO_DATE(?, '%d/%m/%Y'),?,?,?,?)";

		try {
			PreparedStatement stmt = conn.prepareStatement(sql);

			stmt.setString(1, Double.toString(Deb.getValor()));
			stmt.setString(2, Deb.getDescricao());
			stmt.setString(3, Deb.getDatainclusao());
			stmt.setString(4, Deb.getDatavencimento());
			stmt.setString(5, Integer.toString(Deb.getParcelas()));
			stmt.setString(6, Deb.getTipo());
			stmt.setString(7, Integer.toString(Deb.getIdusuario()));

			stmt.executeUpdate();

		} catch (SQLException ex) {

		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
			}
		}
	}

	public void update(ContaPagar Deb, String id) {
		String sql = "UPDATE contapagar SET valor = ?, descricao = ?, datainclusao = STR_TO_DATE(?, '%d/%m/%Y'), parcelas = ?, tipo = ?"
				+ " WHERE idcontaPagar = ?";

		try {
			PreparedStatement stmt = conn.prepareStatement(sql);

			stmt.setString(1, Double.toString(Deb.getValor()));
			stmt.setString(2, Deb.getDescricao());
			stmt.setString(3, Deb.getDatainclusao());
			stmt.setString(4, Integer.toString(Deb.getParcelas()));
			stmt.setString(5, Deb.getTipo());
			stmt.setString(6, id);

			stmt.executeUpdate();

		} catch (SQLException ex) {

		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
			}
		}
	}

	public void delete(String id) {
		String sql = "DELETE FROM contapagar WHERE idcontaPagar = ?";

		try {
			PreparedStatement stmt = conn.prepareStatement(sql);
			stmt.setString(1, id);
			stmt.executeUpdate();
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
	}

	public ContaPagar getByID(String idcontaPagar) {

		String sql = "SELECT * FROM contapagar WHERE idcontaPagar = ?";
		ContaPagar Deb = null;

		try {
			PreparedStatement stmt = conn.prepareStatement(sql);

			stmt.setString(1, idcontaPagar);

			ResultSet rs = stmt.executeQuery();

			if (rs.next()) {
				Deb = new ContaPagar();

				Deb.setIdcontapagar(rs.getInt("idcontaPagar"));
				Deb.setValor(rs.getDouble("valor"));
				Deb.setDatainclusao(rs.getString("datainclusao"));
				Deb.setDatavencimento(rs.getString("datavencimento"));
				Deb.setDescricao(rs.getString("descricao"));
				Deb.setParcelas(rs.getInt("parcelas"));
				Deb.setTipo(rs.getString("tipo"));
				Deb.setIdusuario(rs.getInt("idusuario"));
			}

		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {

		}

		return Deb;

	}

	public List<ContaPagar> getAll(int id) {

		String sql = "SELECT * FROM contapagar where idusuario = ?";

		List<ContaPagar> lista = new ArrayList<ContaPagar>();

		try {
			PreparedStatement stmt = conn.prepareStatement(sql);
			stmt.setString(1, Integer.toString(id));
			SimpleDateFormat formatter = new SimpleDateFormat("dd-MM-yyyy");
			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {
				ContaPagar Deb = new ContaPagar();

				Deb.setIdcontapagar(rs.getInt("idcontaPagar"));
				Deb.setValor(rs.getDouble("valor"));
				Deb.setDatainclusao(rs.getString("datainclusao"));
				Deb.setDatavencimento(rs.getString("datavencimento"));
				Deb.setTipo(rs.getString("tipo"));
				Deb.setDescricao(rs.getString("descricao"));
				Deb.setParcelas(rs.getInt("parcelas"));
				Deb.setIdusuario(rs.getInt("idusuario"));

				lista.add(Deb);
			}

		} catch (SQLException ex) {

		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
			}
		}

		return lista;
	}
}
